﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace estiglp1112_5959_csharp
{
    public partial class StatisticsControl : UserControl
    {
        private DataTable dataTable = new DataTable();
        private string conString;

        /// <summary>
        /// Initializes a new instance of StatisticsControl Class
        /// </summary>
        /// <param name="Parent">The parent control</param>
        /// <param name="ConnectionString">The connection string to access the database</param>
        public StatisticsControl(Control Parent, String ConnectionString)
        {
            this.InitializeComponent();
            this.conString = ConnectionString;
            this.Parent = Parent;
            this.Dock = DockStyle.Fill;
            this.Show();
            this.Select();
            this.RefreshData();
        }

        private void modeComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.RefreshSubSelectionMode(this.modeComboBox.SelectedIndex == 0);
            //this.RefreshChart();
        }

        private void estabComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            RefreshChart();
        }

        private void modeSubSelectionComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            RefreshChart();
        }

        /// <summary>
        /// Clears all data and requeries to database.
        /// </summary>
        public void RefreshData()
        {
            //Connects to database
            SQLiteConnection connection = new SQLiteConnection(this.conString);
            connection.Open();
            SQLiteCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT * FROM DOCENTES";

            //Fills dataTable
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
            dataAdapter.Fill(this.dataTable);

            //Closes the connection and disposes
            connection.Close();
            command.Dispose();
            dataAdapter.Dispose();

            //Fills the establishment comboBox

            var establishmentsList = //=============================
                //#LINQ ============================================
                from column in dataTable.AsEnumerable()
                orderby column.Field<string>("Nome_Estabelecimento")
                select column.Field<string>("Nome_Estabelecimento");
                //#=================================================

            //disables comboBoxes event handlers
            this.modeComboBox.SelectedIndexChanged -= this.modeComboBox_SelectedIndexChanged;
            this.modeSubSelectionComboBox.SelectedIndexChanged -= this.modeSubSelectionComboBox_SelectedIndexChanged;
            this.estabComboBox.SelectedIndexChanged -= this.estabComboBox_SelectedIndexChanged;

            //fills comboBoxes
            this.modeComboBox.SelectedIndex = 0;
            this.estabComboBox.DataSource = establishmentsList.Distinct().ToList();
            this.RefreshSubSelectionMode(true);

            //enable comboBoxes event Handlers
            this.modeComboBox.SelectedIndexChanged += this.modeComboBox_SelectedIndexChanged;
            this.modeSubSelectionComboBox.SelectedIndexChanged += this.modeSubSelectionComboBox_SelectedIndexChanged;
            this.estabComboBox.SelectedIndexChanged += this.estabComboBox_SelectedIndexChanged;

            this.RefreshChart();
        }

        /// <summary>
        /// Refreshes the modeSubSelectionComboBox based on modeComboBox selection (category or degree)
        /// </summary>
        /// <param name="mode">True to list categories, otherwise lists by degree.</param>
        public void RefreshSubSelectionMode(bool listByCategory)
        {
            var modeQuery = //===============================================
                //#LINQ =====================================================
                from table in dataTable.AsEnumerable()
                orderby table.Field<string>(listByCategory ? "Categoria" : "Grau")
                select table.Field<string>(listByCategory ? "Categoria" : "Grau");
                //#==========================================================
            this.modeSubSelectionComboBox.DataSource = modeQuery.Distinct().ToList();
        }

        /// <summary>
        /// Refreshes the data in the chart.
        /// </summary>
        public void RefreshChart()
        {
            var filter = //==============================
                //#LINQ =================================
                from table in dataTable.AsEnumerable()
                where table.Field<string>((string)(modeComboBox.SelectedIndex == 0 ? "Categoria": "Grau")) ==
                    (string)modeSubSelectionComboBox.SelectedItem
                   && table.Field<string>("Nome_Estabelecimento") == (string)estabComboBox.SelectedItem
                group table by table.Field<int>("Ano") into grp //Agrupa por ano
                let count = grp.Count()
                select new
                 {
                    Year = grp.Key,
                    Count = grp.Count()
                };
                //#======================================

            this.chart.DataSource = filter;
            this.chart.DataBind();
        }
    }
}
